#nullable enable

using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using PHMEE.CommonUtils;
using PHMEE.TDengineInfrastructure;

namespace PHMEE.EnergyEfficiency;

public class DigitalTDRepository : BaseTdRepository, IDigitalTDRepository
{
    /// <summary>
    /// Digital 位于 Ams 库
    /// </summary>
    private const DbPrefixEnum DbPrefix = DbPrefixEnum.Ams;
    
    private readonly ILogger<DigitalTDRepository> _logger;

    public DigitalTDRepository(IOptionsSnapshot<TDengineOptions> tdengineOptions, ILogger<DigitalTDRepository> logger) : base(tdengineOptions)
    {
        _logger = logger;
    }

    /// <summary>
    /// 查询 digital 超级表下的每一张子表中的 utcNow 之前的最新一条数据
    /// </summary>
    /// <param name="vesselId"></param>
    /// <param name="dateTime"></param>
    /// <returns></returns>
    public async Task<List<Digital>> GetLatestRecordBeforeDateTimeFromSubtablesAsync(Guid vesselId, DateTime dateTime)
    {
        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

            List<Digital> digitalSignals = [];

            await using var command = TDConnection.CreateCommand();

            command.CommandText =
                $"SELECT ts, LAST(v) as v, tagname, tagdesc, ams_group, group_index FROM {dbName}.digital WHERE ts <= '{dateTime.ToFormattedUtcString()}' GROUP BY tbname;";

            await using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                digitalSignals.Add(new Digital
                {
                    Ts = reader.GetDateTime(0).ToUniversalTime(),
                    V = (DigitalValue)reader.GetInt32(1),
                    TagName = reader.GetString(2),
                    TagDesc = reader.GetString(3),
                    AmsGroup = reader.GetString(4),
                    GroupIndex = reader.GetInt32(5),
                });
            }

            return digitalSignals;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(DigitalTDRepository)}.{nameof(GetLatestRecordBeforeDateTimeFromSubtablesAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, null, dateTime, null, "查询 Digital 数据失败", ex);
        }
    }

    /// <summary>
    /// 查询 digital 超级表下的每一张子表中的 utcNow 之前的最新一条数据，根据 ams_group 筛选
    /// </summary>
    /// <param name="vesselId"></param>
    /// <param name="dateTime"></param>
    /// <param name="amsGroup"></param>
    /// <returns></returns>
    public async Task<List<Digital>> GetLatestRecordBeforeDateTimeByAmsGroupFromSubtablesAsync(Guid vesselId, DateTime dateTime, string amsGroup)
    {
        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

            List<Digital> digitalSignals = [];

            await using var command = TDConnection.CreateCommand();

            command.CommandText =
                $"SELECT ts, LAST(v) as v, tagname, tagdesc, ams_group, group_index FROM {dbName}.digital WHERE ts <= '{dateTime.ToFormattedUtcString()}' AND ams_group = '{amsGroup}' GROUP BY tbname;";

            await using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                digitalSignals.Add(new Digital
                {
                    Ts = reader.GetDateTime(0).ToUniversalTime(),
                    V = (DigitalValue)reader.GetInt32(1),
                    TagName = reader.GetString(2),
                    TagDesc = reader.GetString(3),
                    AmsGroup = reader.GetString(4),
                    GroupIndex = reader.GetInt32(5),
                });
            }

            return digitalSignals;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(DigitalTDRepository)}.{nameof(GetLatestRecordBeforeDateTimeByAmsGroupFromSubtablesAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, null, dateTime, null, "根据 ams_group 查询 Digital 数据失败", ex);
        }
    }

    /// <summary>
    /// 查询 digital 超级表下的 startDateTime 和 endDateTime 之间的数据，不包含分组信息
    /// </summary>
    /// <param name="vesselId"></param>
    /// <param name="startDateTime"></param>
    /// <param name="endDateTime"></param>
    /// <returns></returns>
    [Obsolete]
    public async Task<List<Digital>> GetDigitalAlarmRecordsAsync(Guid vesselId, DateTime startDateTime, DateTime endDateTime)
    {
        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

            List<Digital> digitalSignals = [];

            await using var command = TDConnection.CreateCommand();

            command.CommandText =
                $"SELECT ts, v, tagname, tagdesc FROM {dbName}.digital WHERE ts >= '{startDateTime.ToFormattedUtcString()}' AND ts < '{endDateTime.ToFormattedUtcString()}' ORDER BY ts DESC;";

            await using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                digitalSignals.Add(new Digital
                {
                    Ts = reader.GetDateTime(0).ToUniversalTime(),
                    V = (DigitalValue)reader.GetInt32(1),
                    TagName = reader.GetString(2),
                    TagDesc = reader.GetString(3)

                    // 不查询分组信息

                });
            }

            return digitalSignals;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(DigitalTDRepository)}.{nameof(GetDigitalAlarmRecordsAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, null, startDateTime, endDateTime, "根据时间范围查询 Digital 数据失败", ex);
        }
    }

    // 根据 tagname 查询 digital 超级表下的 startDateTime 和 endDateTime 之间的数据，不包含分组信息
    [Obsolete]
    public async Task<List<Digital>> GetDigitalAlarmRecordsByTagnameAsync(Guid vesselId, string tagName, DateTime startDateTime, DateTime endDateTime)
    {
        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

            List<Digital> digitalSignals = [];

            await using var command = TDConnection.CreateCommand();

            command.CommandText =
                $"SELECT ts, v, tagname, tagdesc FROM {dbName}.digital WHERE ts >= '{startDateTime.ToFormattedUtcString()}' AND ts < '{endDateTime.ToFormattedUtcString()}' AND tagname LIKE '%{tagName}%' ORDER BY ts DESC;";

            await using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                digitalSignals.Add(new Digital
                {
                    Ts = reader.GetDateTime(0).ToUniversalTime(),
                    V = (DigitalValue)reader.GetInt32(1),
                    TagName = reader.GetString(2),
                    TagDesc = reader.GetString(3)

                    // 不查询分组信息

                });
            }

            return digitalSignals;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(DigitalTDRepository)}.{nameof(GetDigitalAlarmRecordsByTagnameAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, null, startDateTime, endDateTime, "根据 tagname 查询 Digital 数据失败", ex);
        }
    }

    // 获取所有 tagdesc
    public async Task<List<string>> GetAllDigitalTagdescAsync(Guid vesselId)
    {
        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

            List<string> allTagdesc = [];

            await using var command = TDConnection.CreateCommand();

            command.CommandText =
                $"SELECT DISTINCT tagdesc FROM {dbName}.digital;";

            await using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                allTagdesc.Add(reader.GetString(0));
            }

            return allTagdesc;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(DigitalTDRepository)}.{nameof(GetAllDigitalTagdescAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, null, null, null, "获取所有 tagdesc 失败", ex);
        }
    }

    // 根据 tagdescription 查询 digital 超级表下的 startDateTime 和 endDateTime 之间的数据，不包含分组信息
    [Obsolete]
    public async Task<List<Digital>> GetDigitalAlarmRecordsByTagdescAsync(Guid vesselId, IEnumerable<string> tagDescs, DateTime startDateTime, DateTime endDateTime)
    {
        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

            List<Digital> digitalSignals = [];

            await using var command = TDConnection.CreateCommand();

            // 将 ' 转义为 ''
            command.CommandText =
                $"SELECT ts, v, tagname, tagdesc FROM {dbName}.digital WHERE ts >= '{startDateTime.ToFormattedUtcString()}' AND ts < '{endDateTime.ToFormattedUtcString()}' AND tagdesc IN ('{string.Join("','", tagDescs.Select(mt => mt.Replace("'", "''")))}') ORDER BY ts DESC;";

            await using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                digitalSignals.Add(new Digital
                {
                    Ts = reader.GetDateTime(0).ToUniversalTime(),
                    V = (DigitalValue)reader.GetInt32(1),
                    TagName = reader.GetString(2),
                    TagDesc = reader.GetString(3)

                    // 不查询分组信息

                });
            }

            return digitalSignals;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(DigitalTDRepository)}.{nameof(GetDigitalAlarmRecordsByTagdescAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, null, startDateTime, endDateTime, "根据 tagdesc 查询 Digital 数据失败", ex);
        }
    }

    /// <summary>
    /// 根据条件，查询 digital 超级表下的 startDateTime 和 endDateTime 之间的数据，不包含分组信息
    /// </summary>
    /// <param name="vesselId"></param>
    /// <param name="tagName"></param>
    /// <param name="tagDescs"></param>
    /// <param name="startDateTime"></param>
    /// <param name="endDateTime"></param>
    /// <param name="skipCount"></param>
    /// <param name="maxResultCount"></param>
    /// <returns></returns>
    public async Task<(int totalCount, List<Digital> digitals)> GetDigitalRecordsAsync(Guid vesselId, string? tagName, List<string>? tagDescs,
        DateTime startDateTime, DateTime endDateTime, int skipCount, int maxResultCount)
    {
        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

            List<Digital> digitalSignals = [];

            List<string> whereConditions =
            [
                $"ts >= '{startDateTime.ToFormattedUtcString()}'",
            $"ts < '{endDateTime.ToFormattedUtcString()}'"
            ];

            if (!string.IsNullOrWhiteSpace(tagName))
            {
                whereConditions.Add($"tagname LIKE '%{tagName}%'");
            }
            if (tagDescs != null && tagDescs.Count != 0)
            {
                whereConditions.Add($"tagdesc IN ('{string.Join("','", tagDescs.Select(mt => mt.Replace("'", "''")))}')");
            }

            string whereClause = whereConditions.Count > 0 ? " WHERE " + string.Join(" AND ", whereConditions) : string.Empty;

            await using var command = TDConnection.CreateCommand();

            // Query total count
            command.CommandText = $"SELECT COUNT(*) FROM {dbName}.digital {whereClause};";
            var totalCount = Convert.ToInt32(await command.ExecuteScalarAsync());

            // Query actual data
            command.CommandText =
                $"SELECT ts, v, tagname, tagdesc FROM {dbName}.digital {whereClause} ORDER BY ts DESC LIMIT {maxResultCount} OFFSET {skipCount};";

            await using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                digitalSignals.Add(new Digital
                {
                    Ts = reader.GetDateTime(0).ToUniversalTime(),
                    V = (DigitalValue)reader.GetInt32(1),
                    TagName = reader.GetString(2),
                    TagDesc = reader.GetString(3)

                    // 不查询分组信息

                });
            }

            return (totalCount, digitalSignals);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(DigitalTDRepository)}.{nameof(GetDigitalRecordsAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, null, startDateTime, endDateTime, "根据条件查询 Digital 数据失败", ex);
        }
    }

    /// <summary>
    /// 查询 digital 超级表下的指定 tagname 的子表中的每一张子表中的 utcNow 之前的最新一条数据
    /// </summary>
    /// <param name="vesselId"></param>
    /// <param name="dateTime"></param>
    /// <param name="tagNames"></param>
    /// <returns></returns>
    public async Task<List<Digital>> GetLatestRecordBeforeDateTimeByTagNamesAsync(Guid vesselId, DateTime dateTime, List<string> tagNames)
    {
        try
        {
            // 根据 tagNames 构建 tbNames
            var tbNames = tagNames.Select(tn => $"d_{tn.ToLower().Trim()}").ToList();
        
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

            List<Digital> digitalSignals = [];

            await using var command = TDConnection.CreateCommand();

            command.CommandText =
                $"SELECT ts, LAST(v) as v, tagname, tagdesc, ams_group, group_index FROM {dbName}.digital WHERE ts <= '{dateTime.ToFormattedUtcString()}' AND tbname IN ('{string.Join("','", tbNames)}') GROUP BY tbname;";

            await using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                digitalSignals.Add(new Digital
                {
                    Ts = reader.GetDateTime(0).ToUniversalTime(),
                    V = (DigitalValue)reader.GetInt32(1),
                    TagName = reader.GetString(2),
                    TagDesc = reader.GetString(3),
                    AmsGroup = reader.GetString(4),
                    GroupIndex = reader.GetInt32(5),
                });
            }

            return digitalSignals;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(DigitalTDRepository)}.{nameof(GetLatestRecordBeforeDateTimeFromSubtablesAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, null, dateTime, null, "查询 Digital 数据失败", ex);
        }
    }
}